package sc.rawdata;

import java.io.*;

import java.text.*;
import java.util.*;

import org.apache.http.client.*;
import org.apache.http.client.methods.HttpGet;

import org.apache.http.impl.client.BasicResponseHandler;
import org.apache.http.impl.client.DefaultHttpClient;

import org.hibernate.*;
import org.hibernate.exception.ConstraintViolationException;

import org.json.*;

import sc.hibernate.config.HibernateSession;
import sc.hibernate.models.*;



/**
 * 
 * 
 * @author Cédric Rais
 *
 */
public class GetRawData {

	//Date format to convert YQL to MySQL
	private static SimpleDateFormat outputDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	
	/**
	 * Get the industry data and symbol per industry
	 * 
	 * @throws HttpException
	 * @throws IOException
	 * @throws JSONException
	 */
	public void getIndustries(){
		
		//Generate sector's ids list
		String response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.sectors&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
		String industryIds = "";
		try{
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results");
	    	JSONArray markets = results.getJSONArray("sector");
	    	for (int i = 0; i < markets.length(); i++) {
	    		JSONObject market = markets.getJSONObject(i); 
	    		JSONArray sectors;
	    		//there is only one sector with id 2xx so its not a JSONArray,
	    		//add it manually to the list
	    		if(i==1){
	    			industryIds = industryIds + "210,";
	    			continue;
	    		}else
	    			sectors = market.getJSONArray("industry");
	    		for (int j = 0; j < sectors.length(); j++) { 
	    			JSONObject sector = sectors.getJSONObject(j); 
	    			industryIds = industryIds + sector.get("id")+",";
	    		}
	    	}
	    }catch(JSONException je){
	    	je.printStackTrace();
	    	return;
	    }
		industryIds = industryIds.substring(0, industryIds.length()-1);

		//Get data from Yahoo
	    response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.industry%20where%20id%20in%20("+ industryIds +")&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
	    ArrayList<String> symbols = new ArrayList<String>();
	    
	    //Parse JSON data
	    try{
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results"); 
	    	JSONArray industries = results.getJSONArray("industry"); 
	    	
	    	//Get industries
	        for (int i = 0; i < industries.length(); i++) { 
	        	Session session = HibernateSession.getSessionFactory().openSession();
	        	session.beginTransaction();	    
	            JSONObject industry = industries.getJSONObject(i); 
	            Sector sector = new Sector(industry.getInt("id"),industry.getString("name"));
	            session.saveOrUpdate(sector);
	            session.getTransaction().commit();
	            session.flush();
	            session.close();
	            
	            JSONArray companies = industry.getJSONArray("company");
	            System.out.println("INDUSTRY : " + industry);
	            //For each industries get companies
	            for(int j=0; j < companies.length(); j++){
	            	//FIXME company not found
	            	JSONObject company = companies.getJSONObject(j);
	            	symbols.add(company.getString("symbol"));
	            	if(symbols.size()>=30){
	            		setStockExchange(sector, symbols.toString());
	            		symbols.clear();
	            	}
	            }   
	        } 
	    }catch(JSONException je){
	    	je.printStackTrace();
	    }catch(HibernateException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Save company per sector
	 * 
	 * @param sectorId the sector id
	 * @param symbols a string that contains all sector's symbols separated by ","
	 */
	private void setStockExchange(Sector sector, String symbols){
		
		Session session = HibernateSession.getSessionFactory().openSession();
		
		String symbolsTmp = symbols.substring(1, symbols.length()-1);
		symbolsTmp = symbolsTmp.replaceAll(" ", "");
		String symbolId;
		
		//YQL to get stock stockexchange
		String response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22"+ symbolsTmp + "%22)&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
		
		try{
			//Parse JSON data
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results");
	    	JSONArray quotes = results.getJSONArray("quote"); 
	
			for(int i=0; i < quotes.length(); i++){
				session.beginTransaction();
				JSONObject quote = quotes.getJSONObject(i);
	    		symbolId = quote.getString("StockExchange") + ":" + quote.getString("symbol"); 
	    		
	    		Float shortRatio = (float) ((quote.get("ShortRatio").equals(null)) ? 0.0 : Float.parseFloat(quote.get("ShortRatio").toString()));
	    		Float PERatio = (float) ((quote.get("PERatio").equals(null)) ? 0.0 : Float.parseFloat(quote.get("PERatio").toString()));
	    		Float PEGRatio = (float) ((quote.get("PEGRatio").equals(null)) ? 0.0 : Float.parseFloat(quote.get("PEGRatio").toString()));
	    		
	    		
	    		Company comp = new Company(symbolId, quote.getString("Name"), 
	    				Float.parseFloat(quote.get("YearLow").toString()), 
	    				Float.parseFloat(quote.get("YearHigh").toString()), 
	    				PERatio, 
	    				PEGRatio,
	    				shortRatio, 
	    				Float.parseFloat(quote.get("BookValue").toString()));
	    		
	    		//FIXME ajoute qu'un sector
	    		sector.getCompanies().add(comp);
	    		comp.getSectors().add(sector);
	    		
	    		//System.out.println("SEC : "+comp.getSectors().size());
	    		//Save company and associate sector
	    		session.saveOrUpdate(comp);
	    		session.getTransaction().commit();
	    		session.flush();
	    	}
			session.close();
		}catch(JSONException je){
	    	je.printStackTrace();
	    }catch(HibernateException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Get daily quote data such as Low, High, Price, Date, Volume. YQL can only
	 * use a few symbol for the resquest so it needs parameters.
	 * 
	 * @param min minimun of the range of company
	 * @param num number of compagny wanted
	 * @throws SQLException
	 */
	public void getDailyData(int min, int count){
		
		Session session = HibernateSession.getSessionFactory().openSession();
		
		
		String symbolsTmp = ""; 
		String tmp = "";
		String [] fullSymbols = null;
		
		try{
			session.beginTransaction();
			//Get 100 companies to add quote
			Query hql = session.createQuery("SELECT symbol FROM Company");
			hql.setFirstResult(min);
			hql.setMaxResults(count);
			List<?> companies = hql.list();
			session.getTransaction().commit();
			
			//Make the correct string company list for YQL
			fullSymbols = new String[companies.size()]; 
			for(int i=0; i < companies.size(); i++){
				tmp = companies.get(i).toString();
				fullSymbols[i] = tmp;
				symbolsTmp = symbolsTmp + tmp.split(":")[1] + ","; 
			}
			symbolsTmp = symbolsTmp.substring(0, symbolsTmp.length()-1);
			
		}catch(HibernateException e){
			e.printStackTrace();
		}finally{
			session.flush();
		}
		
		
		//YQL to get stock stockexchange
		String response = executeYQL("http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quoteslist%20where%20symbol%20in%20(%22"+ symbolsTmp + "%22)&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys");
		
		//Parse JSON data
		try{
			Date date = new Date();
	    	JSONObject json = new JSONObject(response);
	    	JSONObject query = json.getJSONObject("query"); 
	    	JSONObject results = query.getJSONObject("results");
	    	JSONArray quotes = results.getJSONArray("quote"); 
	    	
	    	session.beginTransaction();
			for(int i=0; i < quotes.length(); i++){
				JSONObject quote = quotes.getJSONObject(i);
									
				Query insertQuote = session.createSQLQuery("INSERT INTO quote (SYMBOL, DATE, PRICE, DAILY_LOW, DAILY_HIGH, VOLUME) VALUES ('" + 
	    				fullSymbols[i] +"','"+ outputDateFormat.format(date) +"',"+ quote.getDouble("LastTradePriceOnly") 
	    				+","+ quote.get("DaysLow") +","+ quote.get("DaysHigh")  +","+ quote.get("Volume") +")");
				insertQuote.executeUpdate();	
	    	}
			session.getTransaction().commit();
		
		}catch(JSONException je){
	    	je.printStackTrace();
	    }catch(HibernateException e){
			e.printStackTrace();
		}finally{
			session.flush();
			session.close();
		}
		
	}
	
	/**
	 * Execute YQL method to get financial data
	 * 
	 * @param url : the yahoo YQL url provide by : 
	 * http://developer.yahoo.com/yql/console/?q=show tables&env=store://datatables.org/alltableswithkeys
	 * 
	 * @return the response in JSON format
	 * 
	 * @throws HttpException
	 * @throws IOException
	 */
	private String executeYQL(String url){
		
		HttpClient httpclient = new DefaultHttpClient();
		try{
			HttpGet httpget = new HttpGet(url);
			ResponseHandler<String> responseHandler = new BasicResponseHandler();
			return httpclient.execute(httpget, responseHandler);
		}
		catch(Exception e){
			e.printStackTrace();
			return "";
		}finally {
            // When HttpClient instance is no longer needed,
            // shut down the connection manager to ensure
            // immediate deallocation of all system resources
            httpclient.getConnectionManager().shutdown();
        }
		
	}
	
	
	
	public static void main(String[] args) throws Exception {
		GetRawData g = new GetRawData();

		//Get industry and company per industry
		System.out.println("Sectors and company data...");
		g.getIndustries();
		System.out.println("Sectors and company done");

		/*System.out.println("Get daily data...");
		try{
			//Get daily data 100 by 100
			for(int i=0; i<=30; i++){
				g.getDailyData(i*100,100);
			}
		}catch(StringIndexOutOfBoundsException e){
			System.out.println("End of work :-)");
		}*/
	}
	


}
